
-- part of Extreme T-SQL Script http://etsql.codeplex.com/
-- by Bernd Kriszio https://twitter.com/SQLSulidae  2010-11-01
-- these are helper fubctions to convert sql values into string needed for insert/update statements

-- NOTE: I changed all return values to varchar(max), so that concatenating such terms definitly gives me a varchar(max)
-- most results could be nuch smaller


if object_id ('pp_decimal') > 0 drop function pp_decimal
go

create function dbo.pp_decimal (
	@s decimal (30, 10)
)	returns varchar (max)
as begin
	declare @ret varchar(50)
	set @ret = case when @s is null then 'null' else  cast(@s as varchar ) end
	-- remove trailing decimal zeros
	while right(@ret, 1) = '0'
		set @ret = left(@ret, datalength(@ret) -1)
	-- remove decimal sign, if no fraction part 
	if right(@ret, 1) = '.'
		set @ret = left(@ret, datalength(@ret) -1)
	-- remove thousand seperators	
	return replace (@ret, ',', '')
end
go

/*
select dbo.pp_decimal(5.2)
select dbo.pp_decimal(5.1234)
select dbo.pp_decimal(10e4)
select dbo.pp_decimal(1000)
select dbo.pp_decimal(null)
*/



if object_id ('pp_int') > 0 drop function pp_int
go

create function dbo.pp_int (
	@s bigint
)	returns varchar(max)
as begin
	return case when @s is null then 'null' else  Cast (@s as varchar ) end
end
go


/*
select dbo.pp_int(5)
select dbo.pp_int(null)
*/

if object_id ('pp_string') > 0 drop function pp_string
go

create function dbo.pp_string (
	@s varchar(max),
	@nls bit,
	@oracle bit
)	returns varchar(max)
as begin
	declare @start varchar(5)   set @start = case when @nls <> 0 then 'N''' else '''' end 
	
	return 
	case
		-- null 
		when @s is null then 'null'
		-- MS SQL
	    when @oracle = 0 then @start  + replace (@s, '''', '''''') + ''''
	    -- Oracle starting with '&'
		when left(@s, 1) = '&' 	then 'CHR(38) || '+ @start + replace (replace (replace (replace (substring (@s, 2, 8000), '''', '''''') , char(10), ''' || CHR(10) ||  ' + @start), char(13), ''), '&' , ''' || CHR(38) ||  ' + @start) + ''''
		-- Oracle others
		else @start + replace (replace (replace (replace (@s, '''', '''''') , char(10), ''' || CHR(10) || ' + @start), char(13), ''), '&' , ''' || CHR(38) || ' + @start) + '''' 
		end 
end
go



/*
select dbo.pp_string(null, 0, 0)              -- null
select dbo.pp_string('abcd''ef', 0, 0)        -- 'abcd''ef'
select dbo.pp_string('abcd''ef', 1, 0)        -- N'abcd''ef'
select dbo.pp_string('&a', 0, 0)              -- '&a'
select dbo.pp_string('&a', 1, 0)              -- N'&a'

select dbo.pp_string(null, 0, 1)              -- null
select dbo.pp_string('abcd''ef', 0, 1)        -- 'abcd''ef'
select dbo.pp_string('abcd''ef', 1, 1)        -- N'abcd''ef'
select dbo.pp_string('&a', 0, 1)              -- CHR(38) || 'a'
select dbo.pp_string('&a', 1, 1)              -- CHR(38) || N'a'
select dbo.pp_string('ab&cd', 0, 1)           -- 'ab' || CHR(38) || 'cd'
select dbo.pp_string('ab&cd', 1, 1)           -- N'ab' || CHR(38) || N'cd'
*/



if object_id ('pp_datetime') > 0 drop function pp_datetime
go

create function dbo.pp_datetime (
	@s datetime,
	@oracle bit
)	returns varchar (max)
as begin
	declare @d varchar(10)
	declare @t varchar(8)

	set @d = convert(varchar(8), @s, 112) -- convert(varchar(10), @s, 120) is more readable, but on german system I have to use it with set language us-english LOL
	                                      -- therefore I use pur ISO dateformat
	set @t = convert(varchar(8), @s, 14)
	
	return 
	case when @s is null then 'null' 
	else  '''' 
		+ case when @d = '19000101' and @oracle = 0 then @t
		       when @t = '00:00:00'                 then @d
		                                            else @d + ' ' + @t 
		end
		+ '''' 
	end
end
go

/*
select dbo.pp_datetime('12:45:20', 0)
select dbo.pp_datetime('2005-04-28', 0)
select dbo.pp_datetime(getdate(), 0)
select dbo.pp_datetime(null, 0)

select dbo.pp_datetime('12:45:20', 1)
select dbo.pp_datetime('2005-04-28', 1)
select dbo.pp_datetime(getdate(), 1)
select dbo.pp_datetime(null, 1)

*/

